﻿CREATE PROCEDURE [dbo].[admin_weeklyTimeReport] AS

BEGIN

SET NOCOUNT ON

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = DateAdd(day,-700,GetDate())
SET @endDate = DateAdd(day,0,GetDate())

PRINT '---------------------------------------'
PRINT 'CHO TIME REPORT FROM ' +  CONVERT(VARCHAR(13),@startDate,110) + ' to ' + CONVERT(VARCHAR(13),@endDate,110)
PRINT '---------------------------------------'


PRINT '---------------------------------------'
PRINT 'Hours by Project'
PRINT '---------------------------------------'

SELECT
  CAST(at.name AS VARCHAR(30)) AS Activity,
  CASE WHEN SUM(tl.duration) > 0 
    THEN SUM(tl.duration) / 60
    ELSE 0 
  END AS HoursLogged
  FROM
    timeLog tl
    INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
  WHERE 
    tl.createDate BETWEEN @startDate AND @endDate
  GROUP BY
   at.name
WITH ROLLUP
PRINT '---------------------------------------'
PRINT 'Hours by Project by Day'
PRINT '---------------------------------------'

SELECT
 CAST(Activity AS VARCHAR(30)) AS Activity,
 CASE CAST(IsNull([Monday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Monday],0) AS VARCHAR(4)) END AS Monday,
 CASE CAST(IsNull([Tuesday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Tuesday],0) AS VARCHAR(4)) END AS Tuesday,
 CASE CAST(IsNull([Wednesday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Wednesday],0) AS VARCHAR(4)) END AS Wednesday,
 CASE CAST(IsNull([Thursday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Thursday],0) AS VARCHAR(4)) END AS Thursday,
 CASE CAST(IsNull([Friday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Friday],0) AS VARCHAR(4)) END AS Friday,
 CASE CAST(IsNull([Saturday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Saturday],0) AS VARCHAR(4)) END AS Saturday,
 CASE CAST(IsNull([Sunday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Sunday],0) AS VARCHAR(4)) END AS Sunday
FROM
  (
  SELECT
    CASE DATEPART(dw,tl.createDate)
      WHEN 1 THEN 'Sunday'
      WHEN 2 THEN 'Monday'
      WHEN 3 THEN 'Tuesday'
      WHEN 4 THEN 'Wednesday'
      WHEN 5 THEN 'Thursday'
      WHEN 6 THEN 'Friday'
      WHEN 7 THEN 'Saturday'
    END AS "WorkDay",
    at.name AS Activity,
    CASE WHEN SUM(tl.duration) > 0 
      THEN SUM(tl.duration) / 60
      ELSE 0 
    END AS MinutesLogged
  FROM
    timeLog tl
    INNER JOIN activityType at ON at.activityTypeId = tl.activityTypeId
    INNER JOIN role r ON r.roleId = at.roleId
    INNER JOIN company c ON c.companyId = r.companyId
    INNER JOIN companyUser cu ON cu.companyId = c.companyId
  WHERE 
    tl.createDate BETWEEN @startDate AND @endDate
  GROUP BY
   DATEPART(dw,tl.createDate),
   at.name
  ) AS sourceTable
PIVOT
  (
  SUM(MinutesLogged)
  FOR WorkDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
  ) AS pivotTable


PRINT '---------------------------------------'
PRINT 'Hours by Resource by Day'
PRINT '---------------------------------------'

SELECT
 CAST(Resource AS VARCHAR(30)) AS Resource,
 CASE CAST(IsNull([Monday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Monday],0) AS VARCHAR(4)) END AS Monday,
 CASE CAST(IsNull([Tuesday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Tuesday],0) AS VARCHAR(4)) END AS Tuesday,
 CASE CAST(IsNull([Wednesday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Wednesday],0) AS VARCHAR(4)) END AS Wednesday,
 CASE CAST(IsNull([Thursday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Thursday],0) AS VARCHAR(4)) END AS Thursday,
 CASE CAST(IsNull([Friday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Friday],0) AS VARCHAR(4)) END AS Friday,
 CASE CAST(IsNull([Saturday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Saturday],0) AS VARCHAR(4)) END AS Saturday,
 CASE CAST(IsNull([Sunday],0) AS VARCHAR(4)) WHEN 0 THEN '' ELSE CAST(IsNull([Sunday],0) AS VARCHAR(4)) END AS Sunday
FROM
  (
  SELECT
    CASE DATEPART(dw,tl.createDate)
      WHEN 1 THEN 'Sunday'
      WHEN 2 THEN 'Monday'
      WHEN 3 THEN 'Tuesday'
      WHEN 4 THEN 'Wednesday'
      WHEN 5 THEN 'Thursday'
      WHEN 6 THEN 'Friday'
      WHEN 7 THEN 'Saturday'
    END AS "WorkDay",
    u.screenName AS Resource,
    CASE WHEN SUM(tl.duration) > 0 
      THEN SUM(tl.duration) / 60
      ELSE 0 
    END AS MinutesLogged
  FROM
    timeLog tl
    INNER JOIN users u ON u.userId = tl.userId
  WHERE 
    tl.createDate BETWEEN @startDate AND @endDate
  GROUP BY
   DATEPART(dw,tl.createDate),
   u.screenName
  ) AS sourceTable
PIVOT
  (
  SUM(MinutesLogged)
  FOR WorkDay IN ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
  ) AS pivotTable


END